package com.java.stateMent;

import org.junit.Test;

import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.time.temporal.TemporalAccessor;
import java.util.Calendar;
import java.util.Properties;
import java.util.Scanner;

/**
 * @author Jiange
 * @create 2021-03-30-15:37
 * StateMent:存在sql注入问题 需要拼写sql语句
 * PrepatedStatement比较安全 是Statement的子接口 预编译
 * 查有返回值
 * 增删改没有返回值
 * CallableMent 存储过程
 * PrepareStatement解决了sql注入问题  预编译 可以操作Blob数据 实现更高效的批量操作
 */
public class StateMentTest {
    @Test
    public void test1() throws Exception {
        InputStream inputStream = new FileInputStream("JDBC.properties");
        Properties properties = new Properties();
        properties.load(inputStream);
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driverClass = properties.getProperty("driverClass");
        String url = properties.getProperty("url");

        Class.forName(driverClass);
        Connection connection = DriverManager.getConnection(url, user, password);

        String select = "select * from `students`";
        String insert = "insert into `students`(sno,sname,sex,age) values(?,?,?,?)";//?表示占位符

        //预编译sql语句，返回PreparedStatement实例
        PreparedStatement preparedStatement = connection.prepareStatement(select);
//        preparedStatement.setString(1,"lai");
//        preparedStatement.setString(2,"建哥");
//        preparedStatement.setInt(4,20);

//        preparedStatement.setString(3,"男");
//        preparedStatement.setDate(2, new Date(446545654564654l));
//        boolean execute = preparedStatement.execute();
        ResultSet resultSet = preparedStatement.executeQuery();
        //获取元数据
        ResultSetMetaData metaData = resultSet.getMetaData();
        String columnName = metaData.getColumnName(1);//获取列的表内的名字 不能获取别名
        String columnLabel = metaData.getColumnLabel(1);//获取别名 没有别名就还是原来的列名
        int columnCount = metaData.getColumnCount();
        while (resultSet.next()){//判断集合内是否还有下一个元素 有的话 返回true 下移指针 没有返回false指针不下移
            int sno = resultSet.getInt(1);
            String sname = resultSet.getString(2);
            System.out.println(sno+"------"+sname);
        }

        connection.close();
        preparedStatement.close();
        System.out.println(resultSet);
    }
    @Test
    public void test2() throws Exception {
        //orm思想 表中的一条数据就是一个对象 一个字段就是一个属性
        InputStream inputStream = new FileInputStream("JDBC.properties");

        Properties properties = new Properties();
        properties.load(inputStream);

        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driverClass = properties.getProperty("driverClass");
        String url = properties.getProperty("url");

        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection(url, user, password);

        String select = "select * from `Students`";
        String update = "update students set dept=? where sno=?";

//        PreparedStatement preparedStatement = connection.prepareStatement(select);
//        ResultSet resultSet = preparedStatement.executeQuery();
//        ResultSetMetaData metaData = resultSet.getMetaData();
//        while (resultSet.next()){
//            System.out.println(metaData.getColumnLabel(1)+":"+resultSet.getString(1)+"----------"+metaData.getColumnName(3)+resultSet.getString(3));
//        }
        PreparedStatement preparedStatement = connection.prepareStatement(update);
        preparedStatement.setString(1,"IS");
        preparedStatement.setInt(2,5);
        preparedStatement.execute();//当执行的是更新操作时 返回false 有结果集的返回true
    }
    @Test
    public void test3() throws Exception {
        FileInputStream fileInputStream = new FileInputStream("JDBC.properties");
        Properties properties = new Properties();
        properties.load(fileInputStream);
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driverClass = properties.getProperty("driverClass");

        Class.forName(driverClass);

        String select = "select * from `students`";
        String update = "update `students` set dept=? where sno =?";

        Connection connection = DriverManager.getConnection(url,user,password);

        PreparedStatement preparedStatement = connection.prepareStatement(update);
        preparedStatement.setString(1,"MA");
        preparedStatement.setInt(2,5);

        preparedStatement.execute();
        //preparedStatement.executeUpdate(); 返回修改了的行数

//        ResultSet resultSet = preparedStatement.executeQuery();
//        ResultSetMetaData metaData = resultSet.getMetaData();

//        while (resultSet.next()){
//            System.out.println(metaData.getColumnLabel(1)+":"+resultSet.getString(1)+"-----------"+metaData.getColumnLabel(2)+":"+resultSet.getString(2));
//        }
    }
}
